﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.Linq;
using System.Text;

namespace Excel2SQLBulkTransfer
{
    class DB
    {
        public static string BuildExlConnStr(string sFilename)
        {
            return "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + sFilename + ";Extended Properties=Excel 12.0;";
        }

        public static string BuildSqlConnStr(string sServer, string sDataBase)
        {
            return string.Format("server={0};Trusted_Connection=yes;database={1};", sServer, sDataBase);
        }

        public static string BuildSqlConnStrServerOnly(string sServer)
        {
            return string.Format("server={0};Trusted_Connection=yes;", sServer);
        }

        public static DataTable GetExcel2DataTable(string sFileName, string SheetName)
        {
            OleDbConnection excelConn = new OleDbConnection(BuildExlConnStr(sFileName));

            excelConn.Open();

            OleDbDataAdapter adp = new OleDbDataAdapter("select * from [" + SheetName + "]", excelConn);
            DataSet ds2 = new DataSet();

            adp.Fill(ds2);

            return ds2.Tables[0];
        }

        public static List<string> GetExcelSheetNames(string sFileName)
        {
            OleDbConnection objConn = null;
            System.Data.DataTable dt = null;

            string sConn = BuildExlConnStr(sFileName);

            try
            {
                objConn = new OleDbConnection(sConn);

                objConn.Open();

                dt = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

                if (dt == null)
                {
                    return null;
                }

                List<string> excelSheets = new List<string>();

                // Add the sheet name to the string array.
                foreach (DataRow row in dt.Rows)
                {
                    excelSheets.Add(row["TABLE_NAME"].ToString());
                }

                return excelSheets;
            }
            catch (InvalidOperationException IOE)
            {
                throw IOE;
            }
            finally
            {
                // Clean up.
                if (objConn != null)
                {
                    objConn.Close();
                    objConn.Dispose();
                }
                if (dt != null)
                {
                    dt.Dispose();
                }
            }
        }


        public static void BulkImport2SQL(string sServer, string sDataBase, string TargetTable, DataTable dt)
        {
            try
            {
                using (SqlBulkCopy bulkCopy = new SqlBulkCopy(BuildSqlConnStr(sServer, sDataBase)))
                {
                    bulkCopy.DestinationTableName = TargetTable;
                    bulkCopy.WriteToServer(dt);
                }
            }
            catch (SqlException sqlEx)
            {
                throw sqlEx;
            }
            catch (Exception Ex)
            {
                throw Ex;
            }
                
        }

        public static List<string> GetDataBaseTables(string sServer, string sDataBase)
        {
            List<string> TableList = new List<string>();

            using (SqlConnection connection = new SqlConnection(BuildSqlConnStr(sServer, sDataBase)))
            {
                SqlCommand command = connection.CreateCommand();
                command.CommandText = "select  (TABLE_SCHEMA + '.' + TABLE_NAME) As  TableName from INFORMATION_SCHEMA.Tables where TABLE_TYPE = 'BASE TABLE' order by TableName";
                connection.Open();
                
                using (SqlDataReader sqlDr = command.ExecuteReader())
                {
                    while (sqlDr.Read())
                    {
                        TableList.Add(sqlDr["TableName"].ToString());
                    }
                }
            }

            return TableList;
        }

        public static List<string> GetDataBaseNames(string sServer)
        {
            List<string> DataBaseNamesList = new List<string>();

            using (SqlConnection SqlConn = new SqlConnection(BuildSqlConnStrServerOnly(sServer)))
            {
                SqlConn.Open();

                DataTable dtDBNames = SqlConn.GetSchema("Databases");

                foreach (DataRow row in dtDBNames.Rows)
                {
                    DataBaseNamesList.Add(row["database_name"].ToString());
                }
            }

            return DataBaseNamesList;
        }


    }
}
